package com.one.blocks.jdbc.meta.dialect;

import com.one.blocks.jdbc.meta.CatalogMeta;
import com.one.blocks.jdbc.meta.ColumnMeta;
import com.one.blocks.jdbc.meta.TableMeta;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.LinkedHashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.function.Predicate;

/**
 * @author <a href="mailto:idler41@163.con">linfuxin</a> created on 2024-07-16 20:17:45
 */
public class OracleDialect implements MetaDialect {
    private static final String CATALOG_SQL = "SELECT USERNAME FROM ALL_USERS";

    private static final String TABLE_SQL = "SELECT TABLE_NAME, COMMENTS FROM USER_TAB_COMMENTS WHERE TABLE_NAME IN (SELECT TABLE_NAME FROM USER_TABLES)";

    private static final String COMMENT_SQL = "SELECT COLUMN_NAME, COMMENTS FROM ALL_COL_COMMENTS WHERE OWNER = ? AND TABLE_NAME = ?";

    private static final String COLUMN_SQL = "SELECT COLUMN_NAME, DATA_TYPE  ||\n" +
            "CASE\n" +
            "\tWHEN DATA_PRECISION IS NOT NULL AND DATA_SCALE IS NOT NULL THEN\n" +
            "\t'(' || TO_CHAR( DATA_PRECISION ) || ',' || TO_CHAR( DATA_SCALE ) || ')' ELSE NULL \n" +
            "\tEND AS COLUMN_TYPE \n" +
            "FROM ALL_TAB_COLUMNS \n" +
            "WHERE OWNER = ? AND TABLE_NAME = ?";

    private static final String CATALOG_RESULT = "USERNAME";

    private static final String SUPPORT = "oracle";

    @Override
    public String support() {
        return SUPPORT;
    }

    @Override
    public List<CatalogMeta> getCatalog(DataSource ds) {
        List<CatalogMeta> data = new ArrayList<>();
        try (Connection con = ds.getConnection()) {
            try (Statement stmt = con.createStatement();
                 ResultSet rs = stmt.executeQuery(CATALOG_SQL)) {
                while (rs.next()) {
                    CatalogMeta entity = new CatalogMeta();
                    entity.setCatalogName(rs.getString(CATALOG_RESULT));
                    data.add(entity);
                }
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return data;
    }

    @Override
    public List<TableMeta> getTable(DataSource ds, String catalog, Predicate<String> filter) {
        List<TableMeta> data = new ArrayList<>();
        try (Connection con = ds.getConnection()) {
            try (Statement stmt = con.createStatement();
                 ResultSet rs = stmt.executeQuery(TABLE_SQL)) {
                // 元数据能查表名，但查不到表备注，所以执行sql查询
                while (rs.next()) {
                    String tableName = rs.getString("TABLE_NAME");
                    if (filter == null || filter.test(tableName)) {
                        TableMeta item = new TableMeta();
                        item.setTableName(tableName);
                        item.setTableComment(rs.getString("COMMENTS"));
                        data.add(item);
                    }
                }
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return data;
    }

    @Override
    public List<ColumnMeta> getColumn(DataSource ds, String catalog, String tableName, boolean needColumnType) {
        Map<String, ColumnMeta> data = new LinkedHashMap<>();
        try (Connection con = ds.getConnection()) {
            DatabaseMetaData metaData = con.getMetaData();

            // 列常规元数据
            try (ResultSet columnResult = metaData.getColumns(catalog, null, tableName, null);
                 ResultSet pkResultSet = metaData.getPrimaryKeys(catalog, null, tableName)) {
                Set<String> pkColumnName = new LinkedHashSet<>();
                while (pkResultSet.next()) {
                    pkColumnName.add(pkResultSet.getString(4));
                }
                while (columnResult.next()) {
                    ColumnMeta columnMeta = new ColumnMeta();
                    columnMeta.setDbName(catalog);
                    columnMeta.setTableName(tableName);
                    columnMeta.setColumnName(columnResult.getString("COLUMN_NAME"));
                    columnMeta.setTypeName(columnResult.getString("TYPE_NAME"));
                    columnMeta.setComment(columnResult.getString("REMARKS"));
                    columnMeta.setJdbcType(columnResult.getInt("DATA_TYPE"));
                    columnMeta.setPrecision(columnResult.getInt("COLUMN_SIZE"));
                    columnMeta.setScale(columnResult.getInt("DECIMAL_DIGITS"));
                    // jdbc规范IS_NULLABLE列返回Int,但很多主流数据库并没有遵守规范，比如mysql、pgsql、oracle返回字符串"NO"(不同版本可能有差异)
                    columnMeta.setNullable(columnResult.getObject("IS_NULLABLE"));
                    columnMeta.setIdentity(pkColumnName.contains(columnMeta.getColumnName()));
                    data.put(columnMeta.getColumnName(), columnMeta);
                }
            }

            // 列备注
            try (PreparedStatement stmt = con.prepareStatement(COMMENT_SQL)) {
                stmt.setString(1, catalog);
                stmt.setString(2, tableName);
                try (ResultSet rs = stmt.executeQuery()) {
                    while (rs.next()) {
                        ColumnMeta columnMeta = data.get(rs.getString("COLUMN_NAME"));
                        if (columnMeta != null) {
                            columnMeta.setComment(rs.getString("COMMENTS"));
                        }
                    }
                }
            }

            // 列类型
            if (needColumnType) {
                try (PreparedStatement stmt = con.prepareStatement(COLUMN_SQL)) {
                    stmt.setString(1, catalog);
                    stmt.setString(2, tableName);
                    try (ResultSet rs = stmt.executeQuery()) {
                        while (rs.next()) {
                            ColumnMeta columnMeta = data.get(rs.getString("COLUMN_NAME"));
                            if (columnMeta != null) {
                                columnMeta.setColumnType(rs.getString("COLUMN_TYPE"));
                            }
                        }
                    }
                }
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return new ArrayList<>(data.values());
    }
}
